/* OECD.do (STATA)
	Construct OECD data.
	by Ralph Koijen & Motohiro Yogo */

#delimit ;
clear all;
set more off;
set type double;

cap log close;
log using OECD, replace;

/* Define local and global variables */

local directory = "../../Data/OECD";

do global;


/* Step 1: 720 financial balance sheets */

/* Load data */

u location transact sector measure time Value
	if regexm(transact,"^SAF(33|331|332|5|51|513|52)LINC$")
	& sector=="S1"		/* Total economy */
	& measure=="CXCE"	/* US$ */
	& inrange(time,$year_min,$year_max)
	using `directory'/SNA_TABLE720, clear;

drop sector measure;

/* Rename variables */

rename Value, lower;

/* Fix variables */

replace transact = regexr(transact,"^SAF","LF");
replace transact = "LF3LINC" if transact=="LF33LINC";
replace transact = "LF3SLINC" if transact=="LF331LINC";
replace transact = "LF3LLINC" if transact=="LF332LINC";
replace transact = "LF519LINC" if transact=="LF513LINC";

recode value (0 = .);

replace value = . if location=="COL" & transact=="LF3SLINC" & time==2015;	/* Data error */

/* Save data */

tempfile SNA_TABLE720;
save `SNA_TABLE720';


/* Step 2: 720R (SNA 2008) financial balance sheets */

/* Load data */

u location transact sector measure time Value
	if location!="EU27_2020"
	& regexm(transact,"^LF(3|3S|3L|5|51|519|52)LINC$")
	& sector=="RS1"		/* Total economy */
	& measure=="CXCE"	/* US$ */
	& inrange(time,$year_min,$year_max)
	using `directory'/SNA_TABLE720R, clear;

drop sector measure;

/* Merge 720 financial balance sheets */

merge 1:1 location transact time using `SNA_TABLE720',
	nogen;

/* Rename variables */

rename location Counterpart;
rename time year;
rename Value outstand;

/* Fix variables */

recode outstand (0 = .);

/* Convert to US$ billion */

replace outstand = outstand/1e3;

/* Extrapolate backward if 720R (SNA 2008) missing and 720 available */

gsort Counterpart transact -year;
by Counterpart transact: gen Gvalue = value/value[_n-1];
by Counterpart transact: replace Gvalue = value[_n+1]/value if missing(Gvalue);
by Counterpart transact: replace outstand = outstand[_n-1]*Gvalue if missing(outstand);

drop value Gvalue;

/* Merge country code */

merge m:1 Counterpart using Countries,
	keepusing(Ynat)
	nogen keep(match);

/* Reallocate other equity to fund shares */

egen other = total(outstand*(trans=="LF519LINC")), missing by(year Counterpart);

replace outstand = outstand-other if transact=="LF51LINC";
replace outstand = outstand+other if transact=="LF52LINC";

drop if trans=="LF519LINC";

drop other;

/* Construct variables */

gen byte type = 0 if transact=="LF3LINC";
replace type = 1 if transact=="LF3SLINC";
replace type = 2 if transact=="LF3LLINC";
replace type = 3 if transact=="LF51LINC";
replace type = 4 if transact=="LF52LINC";
replace type = 5 if transact=="LF5LINC";

drop transact;

/* Construct share in common equity and fund shares */

egen Toutstand = total(outstand) if inlist(type,3,4), missing by(year Counterpart);

gen share = outstand/Toutstand if inlist(type,3,4);

drop Toutstand;

/* Expand to common equity and fund shares */

sort year Counterpart type;
by year Counterpart: gen byte obs = type-max(type[_n-1],2) if type==5;

expand obs, gen(duplicate);

bysort year Counterpart type duplicate: replace type = _n+2 if duplicate;

drop if type==5;

drop obs;

/* Extrapolate backward */

gsort Counterpart type -year;
by Counterpart type: replace share = share[_n-1] if missing(share);

/* Assume common equity if breakdown unknown */

replace share = 1 if type==3 & missing(share);

drop if type==4 & missing(share);

/* Impute common equity and fund shares from total */

replace outstand = share*outstand if duplicate;

drop share duplicate;

/* Construct share in ST and LT debt */

egen Toutstand = total(outstand) if inlist(type,1,2), missing by(year Counterpart);

gen share = outstand/Toutstand if inlist(type,1,2);

drop Toutstand;

/* Expand to ST and LT debt */

sort year Counterpart type;
by year Counterpart: gen byte obs = min(type[_n+1],3)-type if type==0;

expand obs, gen(duplicate);

bysort year Counterpart type duplicate: replace type = _n if duplicate;

drop obs;

/* Extrapolate backward */

gsort Counterpart type -year;
by Counterpart type: replace share = share[_n-1] if missing(share);

/* Impute ST and LT debt from All debt */

replace outstand = share*outstand if duplicate;

drop share duplicate;

/* Correct rounding error in total */

egen Toutstand = total(outstand*inlist(type,1,2)), missing by(year Counterpart);

replace outstand = Toutstand if type==0 & Toutstand>0;

drop Toutstand;

/* Drop missing data */

drop if missing(outstand);

/* Drop incomplete data */

drop if Counterpart=="IND" & type>=3;

/* Keep sample of countries */

keep if year>=Ynat;

drop Ynat;

/* Label variables */

order year Counterpart type;

label var year			"Year";
label var Counterpart	"Issuer country by residency";
label var type			"Asset type";

label var outstand		"Amount outstanding (US$ billion)";

label define type_label
	0 "All debt"
	1 "Short-term debt"
	2 "Long-term debt"
	3 "Equity"
	4 "Fund shares";

label val type type_label;

/* Save data */

compress;

sort year Counterpart type;

save OECD, replace;

log close;
